/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.rie.dao;

import com.rie.model.Monitoring;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import rie.model.CPU;
import rie.model.Memoire;
import rie.model.Processus;
import rie.model.SystemeInfo;

/**
 *
 * @author Habib
 */
public class DAO {

    private Connection connection;

    private final String database = "monitoring";
    private final String server = "localhost";
    private final String port = "3306";
    private final String url = "jdbc:mysql://" + server + ":" + port + "/" + database;
   
    public DAO() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url, "monitoring", "");
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void saveQuery(SystemeInfo si, boolean saveCpu, boolean saveMemoire, boolean saveProcess) {
        try {
            Statement statement = connection.createStatement();

            int memId = 0;
            if (saveMemoire) {
                statement.executeUpdate("INSERT INTO MEMORY (freeMemory) VALUES("+si.getMemoire().getFreeRam() + ")", Statement.RETURN_GENERATED_KEYS);
                ResultSet resultMem = statement.executeQuery("SELECT MAX(ID) FROM MEMORY");
                resultMem.next();
                memId = resultMem.getInt(1);
            }
            int cpuId = 0;
            if (saveCpu) {
                statement.executeUpdate("INSERT INTO CPU (cpu) VALUES(" + si.getCpu().getLoadCPU() + ")");
                ResultSet resultCpu = statement.executeQuery("SELECT MAX(ID) FROM CPU");
                resultCpu.next();
                cpuId = resultCpu.getInt(1);
            }
           
            String insertReport = "INSERT INTO REPORT (IP_SERVEUR,ID_MEMORY,ID_CPU,COMMENT) VALUES('" + si.getServeur() + "', " + memId + ", " + cpuId + ", '" + si.getCommentaire() + "')";
            statement.executeUpdate(insertReport);
           
            saveProcess=true;
//            ResultSet resultProcess1 = statement.executeQuery("SELECT MAX(ID) FROM REPORT");
//                resultProcess1.next();
//                int idreport1 = resultProcess1.getInt(1);
//                for (Processus p : si.getListProcessus()) {
//                    String inserProcess1= "INSERT INTO `process` (`ID_REPORT` , `NAME` , `PID` , `MEMORYUSAGE` ) VALUES  (" + idreport1 + ", '" + p.getName() + "', '" + p.getPid() + "', '" + p.getMemoryUsage() + "')";
//                    System.out.println(inserProcess1);
//                }
            System.out.println(saveProcess);
            if (saveProcess) {
                ResultSet resultProcess = statement.executeQuery("SELECT MAX(ID) FROM REPORT");
                resultProcess.next();
                int idreport = resultProcess.getInt(1);
                for (Processus p : si.getListProcessus()) {
                    String inserProcess= "INSERT INTO `process` (`ID_REPORT` , `NAME` , `PID` , `MEMORYUSAGE` ) VALUES  (" + idreport + ", '" + p.getName() + "', '" + p.getPid() + "', '" + p.getMemoryUsage() + "')";
                    System.out.println(inserProcess);
                    statement.executeUpdate(inserProcess, Statement.RETURN_GENERATED_KEYS);
                }
            }
            statement.close();
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public List<String> getServers() {
        List<String> result = new ArrayList<String>();
        try {
            Statement statement = connection.createStatement();
            ResultSet rs3 = statement.executeQuery("SELECT DISTINCT IP_SERVEUR FROM REPORT");
            while (rs3.next()) {
                result.add(rs3.getString(1));
            }
            statement.close();

        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public List<Monitoring> getMonitoring(String server) {
        List<Monitoring> result = new ArrayList<Monitoring>();
        try {
            Statement statement = connection.createStatement();
            ResultSet res = null;
            if (server != null && !server.isEmpty() && !server.equals("*")) {
                res = statement.executeQuery("SELECT * FROM REPORT WHERE IP_SERVEUR LIKE '%" + server + "%'");
            } else {
                res = statement.executeQuery("SELECT * FROM REPORT");
            }
            while (res.next()) {
                result.add(new Monitoring(res.getInt("id"), res.getInt("id_cpu"), res.getInt("id_memory"), res.getInt("process"), res.getString("ip_serveur"), res.getString("date")));
            }
            statement.close();

        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public SystemeInfo getReport(int id) {
        SystemeInfo si = null;
        try {
            Statement sta = connection.createStatement();
            ResultSet result = sta.executeQuery("SELECT * FROM REPORT LEFT JOIN MEMORY ON REPORT.ID_MEMORY=MEMORY.ID LEFT JOIN CPU ON REPORT.ID_CPU=CPU.ID WHERE REPORT.ID=" + id + "");

            if (result.next()) {
                List<Processus> lp = new ArrayList<Processus>();
                si = new SystemeInfo(new CPU(result.getInt("cpu")), new Memoire(result.getInt("freememory")), lp);
                si.setCommentaire(result.getString("comment"));
                si.setServeur(result.getString("ip_serveur"));
                ResultSet rslt = sta.executeQuery("SELECT * FROM PROCESS WHERE ID_REPORT=" + result.getInt("id"));
                while (rslt.next()) {
                    lp.add(new Processus(rslt.getString("name"), rslt.getString("pid"), rslt.getString("memoryusage")));
                }
                si.setListProcessus(lp);
            }
            sta.close();

        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return si;
    }

    public void deleteReportServer(String ipServer) {
        try {
            Statement statement = connection.createStatement();
            statement.executeUpdate("TRUNCATE REPORT");
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
